package com.ebank.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

public class SearchCL extends HttpServlet {
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		HttpSession session=request.getSession();
		PrintWriter out = response.getWriter();
		if(session.getAttribute("username")!=null){
		response.setContentType("text/html;char=utf-8");
		String in=request.getParameter("in");
		String to=request.getParameter("to");
		Statement stat = null;
		Connection con=null;
		ResultSet rs=null;
		PreparedStatement pst = null;
		String selectSQL = "select * from transaction ";
		String loginName=(String) (session.getAttribute("username"));
		 out.print("<input name=Main type=button id=Main onClick=javascript:location.href='/EBanking/Main' value=Main><br><br>");

		
		try {
			 Class.forName("com.mysql.jdbc.Driver");
			 con = DriverManager.getConnection(
				      "jdbc:mysql://localhost/user?useUnicode=true&characterEncoding=Big5",
				      "root","root");
			 pst=con.prepareStatement("SELECT *  FROM transaction WHERE Data between ? and  ? and username=? ORDER BY Data DESC");
			 pst.setObject(1, in);
			 pst.setObject(2, to);
			 pst.setObject(3, loginName);
		     rs=pst.executeQuery();
		     out.print("<table border=1 height=100 width=500 >");
			 out.print("<tr><th>Data</th><th>Username</th><th>In-payment</th><th>out-payment</th><th>Note</th><th>Balance</th></tr>");
			 while(rs.next()){
				 out.print("<tr><td>"+rs.getString(1)+
						 "</td><td>"+rs.getString(2)+
						 "</td><td>"+rs.getDouble(3)+
						 "</td><td>"+rs.getDouble(4)+
						 "</td><td>"+rs.getString(5)+
						 "</td><td>"+rs.getString(6)+
						 "</td></tr>");
			 }
			 out.print("</table>");

		
		    
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				rs=null;
			}
			if(pst!=null){
				try {
					pst.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				pst=null;
			}
			if(con!=null){
				try {
					con.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
				con=null;
			} 
		
		}
		}else{ out.print("Wong Please Login <a href=/EBanking/Login>Login</a>");
		} 
}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		this.doGet(request, response);
	}

}
